package net.bwie.reatime.jtp.mapper;


import net.bwie.reatime.jtp.bean.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface TrafficChannelStatsService {
    @Select("SELECT  sum(paymentAmount) AS uv_count\n" +
            "FROM jtp_app_log.CouponTransaction \n"+
            "WHERE toDate(timestamp) = #{date}"
    )
    List<TrafficControllerAmBean> selectAmCount(@Param("date")String date);

    @Select("SELECT  count(transactionId) AS id_count\n" +
            "FROM jtp_app_log.CouponTransaction \n"+
            "WHERE  toDate(timestamp) = #{date}"
    )
    List<TrafficControllerBcBean> selectBcCount(@Param("date")String date);

    @Select("SELECT count(DISTINCT userId) AS uv_count\n" +
            "FROM jtp_app_log.CouponTransaction\n"+
            "WHERE \n" +
            "couponAmount > 0 AND couponAmount IS NOt NULL AND "+
            "toDate(timestamp) = #{date}"
    )
    List<TrafficControllerCtBean> selectCtCount(@Param("date")String date);
    @Select("SELECT itemName, count(DISTINCT itemId) AS it_count\n" +
            "FROM jtp_app_log.CouponTransaction\n" +
            "WHERE toDate(timestamp) = #{date}\n" +
            "GROUP BY itemName\n" +
            "ORDER BY it_count DESC\n" +
            "LIMIT 100"
    )
    List<TrafficControllerDtBean> selectDtCount(@Param("date")String date);

    @Select("SELECT  city, province,round(sum(paymentAmount),2) AS uv_sum\n" +
            "FROM jtp_app_log.CouponTransaction\n" +
            "WHERE toDate(timestamp) = #{date}\n" +
            "GROUP BY province, city"
    )
    List<TrafficControllerEcBean> selectEcCount(@Param("date")String date);
     @Select("SELECT\n" +
             "    userId,\n" +
             "    transactionId,\n" +
             "    timestamp,\n" +
             "    neighbor(timestamp, 4) AS next_timestamp_4th\n" +
             "FROM (\n" +
             "         SELECT *\n" +
             "         FROM jtp_app_log.CouponTransaction\n" +
             "         WHERE \n" +
             "         timestamp >= now() - INTERVAL 5 MINUTE AND \n" +
             "         toDate(timestamp) = #{date} \n" +
             "         ORDER BY userId, timestamp\n" +
             "         )\n" +
             "WHERE next_timestamp_4th != 0\n" +
             "AND dateDiff('millisecond', timestamp, next_timestamp_4th) <= 60 * 1000 -- 转换为毫秒\n" +
             "ORDER BY userId, timestamp"
        )
    List<TrafficControllerGeBean> selectGeCount(@Param("date")String date);
}
